﻿using System;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;

namespace gensql
{
    class Program
    {
        static void Main(string[] args)
        {
            gensql();
        }

        //文件名
        private const string excelfilename = "LQJH2_数据库设计表.xlsx";
        //目录的sheet名
        private const string contentShtName = "目录";

        private static void gensql()
        {
            Application app = new Application();
            StringBuilder sb = new StringBuilder();

            try
            {
                string filename = System.Environment.CurrentDirectory + "\\" + excelfilename;
                Workbook wb = app.Workbooks.Open(filename);
                Worksheet shtC = wb.Worksheets[contentShtName];
                Worksheet shtT;

                string tname = string.Empty;
                string tengine = string.Empty;
                string cname = string.Empty;
                string ctype = string.Empty;
                string pkey = string.Empty;
                string idx = string.Empty;
                for (int rowC = 4; ; rowC++)
                {
                    tname = shtC.get_Range("C" + rowC, Missing.Value).Text;
                    if (tname == "") break;
                    tengine = shtC.get_Range("D" + rowC, Missing.Value).Text;

                    shtT = wb.Worksheets[tname];

                    sb.AppendFormat("DROP TABLE IF EXISTS `{0}`;\n", tname);
                    sb.AppendFormat("create table `{0}` (\n", tname);

                    pkey = string.Empty;
                    idx = shtT.get_Range("D5", Missing.Value).Text; //索引
                    for (int rowT = 9; ; rowT++)
                    {
                        cname = shtT.get_Range("C" + rowT, Missing.Value).Text;
                        if (cname == "")
                        {
                            sb.Remove(sb.Length - 3, 3); //删除最后一个逗号。包括换行符等，共3个字符
                            sb.AppendLine("");
                            break;
                        }


                        ctype = shtT.get_Range("F" + rowT, Missing.Value).Text;

                        sb.AppendFormat("`{0}` {1} NOT NULL ", cname, ctype);
                        if (shtT.get_Range("E" + rowT, Missing.Value).Text == "是") //自增
                        {
                            sb.Append("AUTO_INCREMENT ");
                        }
                        if (shtT.get_Range("D" + rowT, Missing.Value).Text == "是") //主键
                        {
                            pkey = pkey + "`" + cname + "`,";
                        }

                        if (ctype == "timestamp")
                        {
                            sb.Append("DEFAULT CURRENT_TIMESTAMP ");
                        }
                        sb.AppendLine(",");
                    }

                    if (pkey != "")
                    {
                        sb.AppendLine(",PRIMARY KEY (" + pkey.TrimEnd(',') + ")");
                    }
                    if (idx != "无")
                    {
                        sb.AppendLine("," + idx);
                    }

                    sb.AppendLine(") ENGINE=" + tengine + " DEFAULT CHARSET=utf8;");
                    sb.AppendLine("");
                }

                Logging.Log(sb.ToString());
            }
            catch
            {
                //do nothing
            }

            app.Workbooks.Close();
            GC.Collect();
        }
    }

    /// <summary>
    /// Summary description for Logging.
    /// </summary>
    public class Logging
    {
        static FileAppender appender;

        static Logging()
        {
            string apppath = System.AppDomain.CurrentDomain.BaseDirectory;
            appender = new FileAppender(apppath, "gen.sql", 1024);
        }

        public static void Log(string message)
        {
            appender.Write(message);
        }

        public static void LogWithDateTime(string message)
        {
            appender.Write(DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "\t\t" + message);
        }

        public static void LogWithDateTime(string message, string filename)
        {
            appender.Write(DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "\t\t" + message, filename);
        }
    }

    class FileAppender
    {
        string FileName = "gen.sql";
        string FilePath = string.Empty;

        int MaxFileSize = 1024 * 1024;	//	default FileSize is 1 Mb

        FileStream stream = null;

        public FileAppender(string filePath, string fileName, int maxFileSize)
        {
            if (filePath == null || filePath == string.Empty || filePath == "")
                FilePath = "./";
            else
                FilePath = filePath;


            FilePath = new System.IO.DirectoryInfo(FilePath).FullName;

            if (fileName == null || fileName == string.Empty)
                FileName = System.Diagnostics.Process.GetCurrentProcess().ProcessName + ".log";
            else
                FileName = fileName;

            if (FilePath.Length > 1)
                if (FilePath[FilePath.Length - 1] != '\\' && FileName[0] != '\\')
                    FilePath += "\\";

            if (maxFileSize < 1)
                MaxFileSize = int.MaxValue;
            else
                MaxFileSize = maxFileSize * 1024;

            try
            {
                stream = new FileStream(FilePath + FileName, FileMode.Create, FileAccess.Write, FileShare.ReadWrite);
                stream.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
        }

        public void Write(string s)
        {
            byte[] writes = System.Text.Encoding.UTF8.GetBytes(s + "\r\n");
            Write(writes, FileName);
        }

        public void Write(string s, string p_filename)
        {
            byte[] writes = System.Text.Encoding.UTF8.GetBytes(s + "\r\n");
            Write(writes, p_filename);
        }

        private void Write(byte[] bytes, string p_filename)
        {
            try
            {
                stream = new FileStream(FilePath + p_filename, FileMode.Append, FileAccess.Write, FileShare.ReadWrite);
                stream.Write(bytes, 0, bytes.Length);
                stream.Flush();
                if (stream.Position > MaxFileSize)
                {
                    stream.Close();
                    Rename(p_filename);
                }
                else
                {
                    stream.Close();
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }

        private void Rename(string p_filename)
        {
            System.IO.FileInfo info = new System.IO.FileInfo(FilePath + p_filename);
            string filename = info.FullName.Replace(".log", DateTime.Now.ToString("yyyyMMddHHmmss") + ".log");
            info.MoveTo(filename);
        }
    }
}
